package pro.cherish.knowledgesqltool.core.common;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.OracleHierarchicalExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author 创建人：cherish
 * @version 版本号：V1.0
 * <p>
 * ***************************修订记录************************************
 * 2019-08-30 cherish 创建该类功能。
 * **********************************************************************
 * </p>
 * @ClassName 类名：CommonUtil
 * @Description 功能说明：
 * <p>
 * TODO
 * </p>
 * **********************************************************************
 * @date 创建日期： 2019-08-30 cherish
 */
public class CommonUtil {
    /**
    * @Description: 根据当前环境判断并进行无效语句填充
    * @Param: [stringBuilder, specialFlag]
    * @return: java.lang.StringBuilder
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static StringBuilder handleRespace(StringBuilder stringBuilder ,String specialFlag){
        String str = new String (stringBuilder.toString());
        String str2 = str.trim().toUpperCase();
        specialFlag = specialFlag.trim().toUpperCase();
        if(str2.endsWith("AND")){
            stringBuilder.append(CommonUtil.addBlankSpace(CommonUtil.getOneEqOne()));
        }else if(str2.endsWith("OR")){
            stringBuilder.append(CommonUtil.addBlankSpace(CommonUtil.getOneEqOne()));
        }else if(str2.endsWith("(")) {
            EqualsTo equalsTo2 = null;
            if("OR".equals(specialFlag)){
                equalsTo2=CommonUtil.getOneEqOne();
            }else  if("AND".equals(specialFlag)){
                equalsTo2=CommonUtil.getOneEqOne();
            }else{
                //如果是单个条件进入，则是特殊标记符【specialFlag】便成了like，类型这种需要规避
                equalsTo2=CommonUtil.getOneEqOne();
            }
            stringBuilder.append(CommonUtil.addBlankSpace(equalsTo2));
        }else if(str2.endsWith("")) {
            stringBuilder.append(CommonUtil.addBlankSpace(CommonUtil.getOneEqOne()));
        }
        return stringBuilder;
    }

    /**
    * @Description: 左右两侧添加括号
    * @Param: [str]
    * @return: java.lang.String
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static String addBlankSpace(Object str){
        if(str instanceof  String){
            return " "+str+" ";
        }else{
            return " "+str.toString()+" ";
        }
    }
    /**
    * @Description: 获取原字符串中的所有标签参数
    * @Param: [originStr, pattern]     正则例如 "#\\{.*}"  解析#{ name   }
    * @return: java.util.List<java.lang.String>
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static  List<String> getParam(String originStr,Pattern pattern){
        Matcher matcher = pattern.matcher(originStr);
        List<String> targetParamList = new ArrayList<>();
        while (matcher.find()) {
            String paramName = matcher.group(0).replace("#{", "").replaceAll("}", "").trim();
            targetParamList.add(paramName);
        }
        TreeSet<String> stringTreeSet = new TreeSet<>(targetParamList);
        return  new ArrayList<>(stringTreeSet);
    }

    /**
    * @Description: 简单打印list与string
    * @Param: [object, str]
    * @return: void
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static void  print(Object object,String str){
        System.out.println("======="+str+"=====");
        if(object instanceof List){
            for(int i=0;i<((List) object).size();i++){
                System.out.println("第"+(i+1)+"个----》\n"+((List) object).get(i));
            }
        }else if (object instanceof String){
            System.out.println(object);
        }
        System.out.println("======="+str+"=====");
    }

    /**
    * @Description: 替换旧的where片段替换为处理后的where片段
    * @Param: [originSql, preWhereFragementList, lastWhereFragementList]
    * @return: java.lang.String
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static String replaceStr(String originSql, List<Expression>preWhereFragementList, List<String> lastWhereFragementList ) throws JSQLParserException {
        //格式化原sql语句
        Statement parse = CCJSqlParserUtil.parse(originSql);
        String formatedSql = parse.toString();
        // 中间携带特殊字符无法使用正则替换
        // fixme 在preWhereFragementList存在多个where语句一样，并且都未携带模板，所以更替的sql没有变化，导致第一次替换后formatedSql置没变，第二个搜索的index还是第一个搜索
        // fixme 到的where语句上。由于语句没有变化，所以可以不处理也不会出现问题。并且出现的几率不大。
        for(int i=0;i<preWhereFragementList.size();i++) {
            String preFragement = preWhereFragementList.get(i).toString();
            int fragementBegin = formatedSql.indexOf(preFragement);
            int fragementLength = preFragement.length();
            String preSql = formatedSql.substring(0, fragementBegin);
            String lastSql = formatedSql.substring(fragementBegin + fragementLength);
            formatedSql= new StringBuilder(preSql).append(lastWhereFragementList.get(i)).append(lastSql).toString();
        }
        return formatedSql;
    }

    /**
     * @Description: 替换表达式字符串
     * @Param: [customerSqlTpl, changeExpressionList]
     * @return: void
     * @Author: cherish
     * @Date: 2019/9/6
     */
    public static String replaceExpressionStr(String formatedSql, List<ChangeExpression> changeExpressionList) throws JSQLParserException {

        //fixme 如果需要被替换为1=1，则替换后，
        // 后续重复的配置就不会匹配到被替换的位置。如果原表达式没有被替换，则第二个一模一样的表达式还是匹配到前者，
        // 则再次替换，错过的那个表达式还是保持不变【与替换成一模一样的字符串是一样的效果】，取巧了。
        // 中间携带特殊字符无法使用正则替换
        for(int i=0;i<changeExpressionList.size();i++) {
            String preFragement = changeExpressionList.get(i).getOldExpression();
            int fragementBegin = formatedSql.indexOf(preFragement);
            int fragementLength = preFragement.length();
            String preSql = formatedSql.substring(0, fragementBegin);
            String lastSql = formatedSql.substring(fragementBegin + fragementLength);
            formatedSql= new StringBuilder(preSql).append(changeExpressionList.get(i).getNewExpression()).append(lastSql).toString();
        }
        return formatedSql;

    }

    /**
    * @Description:  获取1 = 1表达式
    * @Param: []
    * @return: net.sf.jsqlparser.expression.operators.relational.EqualsTo
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static EqualsTo getOneEqOne(){
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new LongValue(1));
        equalsTo.setRightExpression(new LongValue(1));
        return equalsTo;
    }

    /**
    * @Description: 获取 1 = 0 表达式
    * @Param: []
    * @return: net.sf.jsqlparser.expression.operators.relational.EqualsTo
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static EqualsTo getOneEqZero(){
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new LongValue(1));
        equalsTo.setRightExpression(new LongValue(0));
        return equalsTo;
    }
    /**
    * @Description: 用于表示修正 false or false 的无效语句
    * @Param: []
    * @return: net.sf.jsqlparser.expression.operators.relational.EqualsTo
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static EqualsTo getZeroEqZero(){
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new LongValue(0));
        equalsTo.setRightExpression(new LongValue(0));
        return equalsTo;
    }


    /**
    * @Description: 修正sql，解决false or false 的问题
    * @Param: [sql]
    * @return: java.lang.String
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static String correctionSql(String sql) {
        // 解决(1=0 or 1=0) and name='lys'的问题【条件全部失效】
        // 拷贝一份字符串进行测试
        String tempSql = new String(sql);
        tempSql = tempSql.replaceAll(" OR ", "");
        Pattern pattern = Pattern.compile(CommonConfig.getOrProblemPartenStr());
        Matcher matcher = pattern.matcher(tempSql);
        if (matcher.find()) {
            String tarStr = matcher.group(0);
            // 找到需要替换的开始索引
            int beginIndex = tempSql.indexOf(tarStr);
            // 找到需要替换的结束索引
            int endIndex = sql.indexOf(")", beginIndex);
            // 截取前半段
            String preSql = StringUtils.substring(sql, 0, beginIndex);
            // 截取后半段
            String lastSql = StringUtils.substring(sql,endIndex+1);
            // 替换中间sql
            sql = new StringBuilder(preSql).append(CommonUtil.addBlankSpace(CommonUtil.getZeroEqZero().toString())).append(lastSql).toString();
        }
        return sql;
    }

    /**
    * @Description: 构建参数串，#{id} =>#{params.id}
    * @Param: [params]
    * @return: java.util.Map<java.lang.String,java.lang.Object>
    * @Author: cherish
    * @Date: 2019/8/31
     */
    public static Map<String, Object> changeMybatisMapParams(Map<String, Object> params) {
        Map<String, Object> sqlParams = new HashMap<String,Object>();
        for (String key : params.keySet()) {
            Object value = params.get(key);
            // 判断参数是用什么类型的方式传输的
            // 如果参数类型是字符串数组
            if (value instanceof String[]) {
                String[] _value = (String[]) value;
                String[] __v = new String[_value.length];
                for (int i = 0; i < _value.length; i++) {
                    __v[i] = "#{params." + key + "[" + i + "]}";
                }
                // 取出sqlParams的value值，用逗号隔开
                sqlParams.put(key, StringUtils.join(__v, ","));
            } else if (value instanceof List) {
                // 如果参数类型是数组
                List<Object> _value = (List<Object>) value;
                String[] __v = new String[_value.size()];
                // 循环遍历 取出value 重新拼装成 #{params.key}的形式R
                for (int i = 0; i < _value.size(); i++) {
                    __v[i] = "#{params." + key + "[" + i + "]}";
                }
                sqlParams.put(key, StringUtils.join(__v, ","));
            } else {
                sqlParams.put(key, "#{params." + key + "}");
            }
        }
        return sqlParams;
    }

    /**
    * @Description: 将标准标签sql转换为mybatis的Map参数格式sql
    * @Param: [sql, userParamMap]
    * @return: java.lang.String
    * @Author: cherish
    * @Date: 2019/8/31
    */
    public static String adaptMybatisSql(String sql,Map<String, Object> userParamMap) {
        Map<String, Object> mybatisMapParams = CommonUtil.changeMybatisMapParams(userParamMap);
        for (Map.Entry<String, Object> entry : userParamMap.entrySet()) {
            // lable
            String key = entry.getKey();
            // 获取参数值
            Object value = userParamMap.get(key);
            // 除了分页的参数外
            if (Arrays.binarySearch(CommonConfig.getPageParam(), key) <= 0) {
                //		#\{[^{]*name[^{]*\}
                // 获取出形如#{param.name}的数据，替换原来的#{name}
                sql = sql.replaceAll(CommonConfig.getParamLabelParanStr2(key), (String) mybatisMapParams.get(key));
            }
        }
        return sql;
    }

    /**
    * @Description: 查询并解析where表达式
    * @Param: [whereExpressionList, selectBody, changeExpressionList, userParamMap]
    * @return: void
    * @Author: cherish
    * @Date: 2019/9/9
    */
    public static void findWhereFragement(List<Expression> whereExpressionList, SelectBody selectBody, List<ChangeExpression> changeExpressionList, Map<String, Object> userParamMap) {
        if (selectBody != null) {
            if (selectBody instanceof PlainSelect) {
                // 连接join
                if (((PlainSelect) selectBody).getJoins() != null && ((PlainSelect) selectBody).getJoins().size() > 0) {
                    for (Join join : ((PlainSelect) selectBody).getJoins()) {
                        FromItem rightItem = join.getRightItem();
                        if (rightItem != null && rightItem instanceof SubSelect) {
                            SelectBody body = ((SubSelect) rightItem).getSelectBody();
                            if (body instanceof PlainSelect) {
                                findWhereFragement(whereExpressionList,body,changeExpressionList,userParamMap);
                            } else if (body instanceof SetOperationList) {
                                List<SelectBody> selects = ((SetOperationList) body).getSelects();
                                for (SelectBody selectBody1 : selects) {
                                    if (selectBody1 instanceof PlainSelect) {
                                        findWhereFragement(whereExpressionList,selectBody1,changeExpressionList,userParamMap);
                                    }
                                }
                            }
                        }
                    }
                }
                // from
                FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
                if (fromItem != null) {
                    if (fromItem instanceof SubSelect) {
                        SubSelect subSelect = (SubSelect) fromItem;
                        // 查询
                        if (subSelect.getSelectBody() instanceof PlainSelect) {
                            findWhereFragement(whereExpressionList,subSelect.getSelectBody(),changeExpressionList,userParamMap);
                        }else if(subSelect.getSelectBody() instanceof  SetOperationList){
                            //联合查询
                            List<SelectBody> selects = ((SetOperationList) subSelect.getSelectBody()).getSelects();
                            if(selects!=null &selects.size()>0){
                                for (SelectBody select : selects) {
                                    findWhereFragement(whereExpressionList,select,changeExpressionList,userParamMap);
                                }
                            }
                        }
                        // 在from的子表达式中存在with的写法
                        List<WithItem> withItems = subSelect.getWithItemsList();
                        if(withItems!=null && withItems.size()>0){
                            for (WithItem withItem : withItems) {
                                SelectBody withItemSelectBody = withItem.getSelectBody();
                                if (withItemSelectBody instanceof PlainSelect) {
                                    findWhereFragement(whereExpressionList,withItemSelectBody,changeExpressionList,userParamMap);
                                }else if(withItemSelectBody instanceof SetOperationList){
                                    List<SelectBody> selects = ((SetOperationList) withItemSelectBody).getSelects();
                                    if(selects!=null &selects.size()>0){
                                        for (SelectBody select : selects) {
                                            findWhereFragement(whereExpressionList,select,changeExpressionList,userParamMap);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                // 单独支持一个oracle 语法 start with 递归语法 // FIXME: 2019/9/9
                OracleHierarchicalExpression oracleHierarchical = ((PlainSelect) selectBody).getOracleHierarchical();
                if(oracleHierarchical!=null && oracleHierarchical instanceof OracleHierarchicalExpression){
                    Expression startExpression =oracleHierarchical.getStartExpression();
                    if(startExpression!=null && startExpression instanceof EqualsTo){
                        Expression rightExpression = ((EqualsTo) startExpression).getRightExpression();
                        List<String> paramList = CommonUtil.getParam(rightExpression.toString(), Pattern.compile(CommonConfig.getAllParamLabelParanStr()));
                        if(paramList!=null && paramList.size()!=0){
                            // 有配模板
                            String key = paramList.get(0);
                            Object value = userParamMap.get(key);
                            if (value == null || "".equals(value)) {
                                changeExpressionList.add(new ChangeExpression(startExpression.toString(),CommonUtil.getOneEqOne().toString(),0,0));
                            }
                        }
                    }
                }

                Expression where = ((PlainSelect) selectBody).getWhere();
                if (where != null) {
                    whereExpressionList.add(where);
                    where.accept(new MyWhereExpressionDeParser(null, new StringBuilder(),changeExpressionList, whereExpressionList,userParamMap));
                }
            } else if (selectBody instanceof SetOperationList) {
                // union
                List<SelectBody> selects = ((SetOperationList) selectBody).getSelects();
                for (SelectBody body : selects) {
                    findWhereFragement(whereExpressionList,body,changeExpressionList,userParamMap);
                }
            }
        }
    }
}
